package cn.iocoder.yudao.module.member.statistics.dao;

import cn.iocoder.yudao.framework.business.basic.dao.mapper.CoreMapper;
import cn.iocoder.yudao.module.member.statistics.pojo.bo.MemberAreaStatisticsRespBO;
import cn.iocoder.yudao.module.member.statistics.pojo.bo.MemberRegisterCountRespBO;
import cn.iocoder.yudao.module.member.statistics.pojo.bo.MemberSexStatisticsRespBO;
import cn.iocoder.yudao.module.member.statistics.pojo.bo.MemberTerminalStatisticsRespBO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 用户信息的统计 Mapper
 *
 * @author owen
 */
@Mapper
@SuppressWarnings("rawtypes")
public interface MemberStatisticsMapper extends CoreMapper {

    @Select("""
            SELECT area_id, COUNT(1) AS userCount FROM member_user WHERE deleted = FALSE GROUP BY area_id
            """)
    List<MemberAreaStatisticsRespBO> selectSummaryListGroupByAreaId();

    @Select("""
            SELECT id FROM member_user WHERE deleted = FALSE AND area_id > 0
            """)
    List<Long> selectUserIdsWhenExistsAreaId();

    @Select("""
            SELECT sex, COUNT(1) AS userCount
            FROM member_user
            WHERE deleted = FALSE
            GROUP BY sex
            """)
    List<MemberSexStatisticsRespBO> selectSummaryListBySex();

    @Select("""
            SELECT register_terminal as terminal, COUNT(1) AS userCount
            FROM member_user
            WHERE deleted = FALSE
            GROUP BY register_terminal
            """)
    List<MemberTerminalStatisticsRespBO> selectSummaryListByRegisterTerminal();

    @Select("""
            <script>
                    SELECT COUNT(1)
                    FROM member_user
                    WHERE deleted = FALSE
                    <if test="beginTime != null">
                        AND create_time <![CDATA[ >= ]]> #{beginTime}
                    </if>
                    <if test="endTime != null">
                        AND create_time <![CDATA[ <= ]]> #{endTime}
                    </if>
            </script>
            """)
    Integer selectUserCount(@Param("beginTime") LocalDateTime beginTime,
                            @Param("endTime") LocalDateTime endTime);

    /**
     * 获得用户的每天注册数量列表
     *
     * @param beginTime 开始时间
     * @param endTime   结束时间
     * @return 每天注册数量列表
     */
    @Select("""
            SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS date, count(1) AS count
            FROM member_user
            WHERE create_time BETWEEN #{beginTime} AND #{endTime} AND deleted = FALSE
            GROUP BY date
            """)
    List<MemberRegisterCountRespBO> selectListByCreateTimeBetween(@Param("beginTime") LocalDateTime beginTime,
                                                                  @Param("endTime") LocalDateTime endTime);

    @Select("""
                    SELECT COUNT(DISTINCT user_ip)
                    FROM infra_api_access_log
                    WHERE user_type = #{userType}
                    AND create_time BETWEEN #{beginTime} AND #{endTime}
                    AND deleted = FALSE
            """)
    Integer selectIpCountByUserTypeAndCreateTimeBetween(@Param("userType") Integer userType,
                                                        @Param("beginTime") LocalDateTime beginTime,
                                                        @Param("endTime") LocalDateTime endTime);

    @Select("""
            SELECT COUNT(DISTINCT user_id)
            FROM infra_api_access_log
            WHERE user_id > 0
              AND user_type = #{userType}
              AND create_time BETWEEN #{beginTime} AND #{endTime}
              AND deleted = FALSE
            """)
    Integer selectUserCountByUserTypeAndCreateTimeBetween(@Param("userType") Integer userType,
                                                          @Param("beginTime") LocalDateTime beginTime,
                                                          @Param("endTime") LocalDateTime endTime);

}
